Database systems are often used to manage sales forces. One common task performed by such systems is the task of determining which sales representatives are associated with particular accounts. The sales representative-to-account mapping is critical for many management decisions, such as determining which sales representative should be sent to pursue a potential sale associated with a particular account.
The factors used to determine the representative-to-account mapping are referred to as qualifiers, and include, for example, geographic limits, zip code zones, customer names, etc. One approach to generate such a mapping involves (1) assigning sales representatives to territories, (2) associating territories with specific criteria, (3) associating accounts with specific qualifier values, and running an SQL query that matches the criteria of the territories qualifier values to the values of the accounts. In this context, a “territory” need not be a geographic territory. Rather, a territory can be defined by any criteria, such as customer names, customer ages, anticipated size of account, etc. For example, a particular territory may be defined to include all customers under the age of 18 with estimated account sizes greater than $100,000.
Unfortunately, an SQL query capable of performing the matching operation can be quite complex. One approach for creating such an SQL query involves generating a static SQL statement that includes every possible combination of qualifiers and operands for such qualifiers. In this approach, the same static SQL statement can be used for each territory by plugging into the statement the specific values to form the criteria for the territory.
For example, a territory X may be associated with the criteria “customer name=ACME” AND “zip=95125”. A territory Y may be associated with the criteria “estimated value>$500000” AND “state=CA”. The same static SQL statement may be used to perform the match operation for both territory X and territory Y if the SQL statement includes all combinations of all possible qualifiers. Specifically, for the territory X operation the SQL statement would use the “customer name” “=” and “zip” “=” combination. For the territory Y matching operation, the SQL statement would use the “estimated value” “>” and “state” “=” combination.
However in using such an approach, the complexity of the SQL statement grows exponentially as new qualifiers are added for defining territories. If different territories have different qualifiers associated with them, then an SQL statement that supports all combinations of qualifiers incurs additional performance overhead. For example, since the static SQL statement supports all qualifiers as predicates, overhead is incurred for each of these qualifiers, regardless of whether a particular territory uses these qualifiers. As can be seen, a new approach is needed where new qualifiers can be added to the set of criteria for defining territories without the SQL statement growing exponentially and without incurring unnecessary performance overhead.